ENTSO-E Electricity Data Analysis¶
Imports¶
In [1]:
import numpy as np
import plotly.graph_objects as go
import plotly.express as px
import pandas as pd
import sys
sys.path.insert(1,'../')
from scripts.plotting_utils import *
Load Processed Data¶
In [2]:
df = pd.read_feather("../data/processed_data.feather")
Analysis and Visualization¶
In [3]:
non_generation = ['dt', 'price', 'demand_forecast', 'demand', 'wind_offshore_forecast', 'solar_forecast', 'wind_forecast', 'wind_onshore_forecast', 'doy', 'doy_season', 'residual', 'vre_forecast', 'hour']
In [4]:
from dotenv import load_dotenv
import os
import entsoe
def get_entsoe_client():
"""Initializes and returns the EntsoePandasClient."""
load_dotenv()
API_KEY = os.environ.get("ENTSOE_API_KEY")
if not API_KEY:
raise ValueError(
"ENTSOE_API_KEY environment variable not set. Please set it in your .env file."
)
client = entsoe.EntsoePandasClient(api_key=API_KEY)
return client
client = get_entsoe_client()
In [5]:
start = min(df.DE_LU.index)
end = max(df.DE_LU.index)
start, end
Out[5]:
(Timestamp('2025-08-16 22:00:00+0000', tz='UTC'),
Timestamp('2025-08-24 22:00:00+0000', tz='UTC'))
In [6]:
country_code = 'FR'
In [7]:
df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False).drop(columns='sum')
In [8]:
# df_imports = df_imports.resample(rule='h').mean()
In [9]:
import_sum = df_imports.ffill().transpose().sum()
import_sum.name='imports'
In [10]:
# df_exports = client.query_physical_crossborder_allborders(country_code='FR', start=start, end=end, export= True, per_hour = True)
df_exports = client.query_physical_crossborder_allborders(country_code='FR', start=start, end=end, export= True, per_hour = False).drop(columns='sum')
In [11]:
df_exports = df_exports.ffill()
df_exports.plot()
In [12]:
df_exports
Out[12]:
| BE | CH | DE_LU | ES | GB | IT_NORD | |
|---|---|---|---|---|---|---|
| 2025-08-17 00:00:00+02:00 | 2683.0 | 1215.0 | 3166.0 | 2705.0 | 4077.0 | 1232.0 |
| 2025-08-17 00:15:00+02:00 | 2618.0 | 1215.0 | 3073.0 | 2396.0 | 4077.0 | 1053.0 |
| 2025-08-17 00:30:00+02:00 | 2671.0 | 1215.0 | 3147.0 | 2407.0 | 4077.0 | 1046.0 |
| 2025-08-17 00:45:00+02:00 | 2712.0 | 1215.0 | 3169.0 | 2341.0 | 4077.0 | 1053.0 |
| 2025-08-17 01:00:00+02:00 | 2590.0 | 1133.0 | 2973.0 | 2351.0 | 4078.0 | 1033.0 |
| ... | ... | ... | ... | ... | ... | ... |
| 2025-08-24 22:45:00+02:00 | 2074.0 | 47.0 | 2974.0 | 1626.0 | 3669.0 | 2793.0 |
| 2025-08-24 23:00:00+02:00 | 2324.0 | 1379.0 | 3066.0 | 1818.0 | 2908.0 | 3035.0 |
| 2025-08-24 23:15:00+02:00 | 2312.0 | 1379.0 | 3088.0 | 1752.0 | 2908.0 | 3047.0 |
| 2025-08-24 23:30:00+02:00 | 2251.0 | 1379.0 | 3028.0 | 1698.0 | 2908.0 | 3042.0 |
| 2025-08-24 23:45:00+02:00 | 2278.0 | 1379.0 | 2982.0 | 1716.0 | 2908.0 | 3075.0 |
768 rows × 6 columns
In [13]:
df_exports.CH.ffill().plot()
In [14]:
df_exports.CH.plot(kind='scatter')
In [15]:
export_sum = df_exports.ffill().transpose().sum()
export_sum.name='exports'
In [16]:
df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')
In [17]:
idx = pd.IndexSlice
In [18]:
# phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']].resample(rule='h').mean()
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)
In [19]:
# dfFR = pd.concat([import_sum, -1 * export_sum, df.FR.drop(columns=non_generation), -1* phs_consumption], axis=1)
dfFR = pd.concat([import_sum, -1 * export_sum, df.FR.drop(columns=non_generation)], axis=1)
# dfFR = pd.concat([-1 * export_sum, df.FR.drop(columns=non_generation), -1* phs_consumption], axis=1)
In [20]:
dfFR
Out[20]:
| imports | exports | biomass | brown_coal | coal_gas | energy_storage | gas | geothermal | hard_coal | hydro_reservoir | ... | oil | oil_shale | other | other_re | peat | pumped_storage | solar | waste | wind_offshore | wind_onshore | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2025-08-16 22:00:00+00:00 | 0.0 | -15078.0 | 228.0 | 0.0 | 0.0 | 3.0 | 1082.0 | 0.0 | 0.0 | 1286.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 2172.0 | 0.0 | 373.0 | 1375.0 | 5612.0 |
| 2025-08-16 22:15:00+00:00 | 0.0 | -14432.0 | 234.0 | 0.0 | 0.0 | 3.0 | 1095.0 | 0.0 | 0.0 | 1184.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 994.0 | 0.0 | 373.0 | 1349.0 | 5485.0 |
| 2025-08-16 22:30:00+00:00 | 0.0 | -14563.0 | 236.0 | 0.0 | 0.0 | 4.0 | 1006.0 | 0.0 | 0.0 | 922.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 530.0 | 0.0 | 372.0 | 1222.0 | 5476.0 |
| 2025-08-16 22:45:00+00:00 | 0.0 | -14567.0 | 237.0 | 0.0 | 0.0 | 2.0 | 824.0 | 0.0 | 0.0 | 825.0 | ... | 70.0 | 0.0 | 0.0 | 0.0 | 0.0 | 459.0 | 0.0 | 374.0 | 1050.0 | 5436.0 |
| 2025-08-16 23:00:00+00:00 | 0.0 | -14158.0 | 239.0 | 0.0 | 0.0 | 2.0 | 686.0 | 0.0 | 0.0 | 857.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 503.0 | 0.0 | 375.0 | 954.0 | 5420.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2025-08-24 21:00:00+00:00 | 0.0 | -14530.0 | 227.0 | 0.0 | 0.0 | 8.0 | 2421.0 | 0.0 | 0.0 | 1533.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1690.0 | 0.0 | 379.0 | 949.0 | 3453.0 |
| 2025-08-24 21:15:00+00:00 | 0.0 | -14486.0 | 227.0 | 0.0 | 0.0 | 4.0 | 2374.0 | 0.0 | 0.0 | 1466.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1062.0 | 0.0 | 378.0 | 947.0 | 3550.0 |
| 2025-08-24 21:30:00+00:00 | 0.0 | -14306.0 | 227.0 | 0.0 | 0.0 | 4.0 | 2240.0 | 0.0 | 0.0 | 1412.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1011.0 | 0.0 | 379.0 | 1005.0 | 3627.0 |
| 2025-08-24 21:45:00+00:00 | 0.0 | -14338.0 | 226.0 | 0.0 | 0.0 | 0.0 | 2022.0 | 0.0 | 0.0 | 1341.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1029.0 | 0.0 | 382.0 | 1011.0 | 3709.0 |
| 2025-08-24 22:00:00+00:00 | NaN | NaN | 226.0 | 0.0 | 0.0 | 0.0 | 2022.0 | 0.0 | 0.0 | 1341.0 | ... | 69.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1029.0 | 0.0 | 382.0 | 1011.0 | 3709.0 |
769 rows × 23 columns
In [21]:
df_net_demand = pd.concat([df.FR.demand,phs_consumption],axis=1)
df_net_demand['net_demand'] = df_net_demand.demand+df_net_demand['Hydro Pumped Storage']
df_net_demand
Out[21]:
| demand | Hydro Pumped Storage | net_demand | |
|---|---|---|---|
| 2025-08-16 22:00:00+00:00 | 40444.0 | 0.0 | 40444.0 |
| 2025-08-16 22:15:00+00:00 | 39275.0 | 2.0 | 39277.0 |
| 2025-08-16 22:30:00+00:00 | 38017.0 | 18.0 | 38035.0 |
| 2025-08-16 22:45:00+00:00 | 37060.0 | 26.0 | 37086.0 |
| 2025-08-16 23:00:00+00:00 | 37242.0 | 158.0 | 37400.0 |
| ... | ... | ... | ... |
| 2025-08-24 21:00:00+00:00 | 40287.0 | 1.0 | 40288.0 |
| 2025-08-24 21:15:00+00:00 | 39471.0 | 1.0 | 39472.0 |
| 2025-08-24 21:30:00+00:00 | 39617.0 | 1.0 | 39618.0 |
| 2025-08-24 21:45:00+00:00 | 38791.0 | 1.0 | 38792.0 |
| 2025-08-24 22:00:00+00:00 | 38791.0 | NaN | NaN |
769 rows × 3 columns
In [22]:
fig = dfFR.plot(kind='area')
fig.add_trace(go.Scatter(y=df_net_demand.net_demand, x=df.FR.dt, mode='lines',line=dict(color='black')))
fig.add_trace(go.Scatter(y=df.FR.demand, x=df.FR.dt, mode='lines',line=dict(color='gray')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
# fig.add_trace(go.Scatter(y=df.FR.demand_forecast, x=df.FR.dt, mode='lines',line=dict(color='gray')))
In [23]:
df_phs
Out[23]:
| Hydro Pumped Storage | ||
|---|---|---|
| Actual Aggregated | Actual Consumption | |
| 2025-08-17 00:00:00+02:00 | 2172.0 | 0.0 |
| 2025-08-17 00:15:00+02:00 | 994.0 | 2.0 |
| 2025-08-17 00:30:00+02:00 | 530.0 | 18.0 |
| 2025-08-17 00:45:00+02:00 | 459.0 | 26.0 |
| 2025-08-17 01:00:00+02:00 | 503.0 | 158.0 |
| ... | ... | ... |
| 2025-08-24 22:45:00+02:00 | 2002.0 | 52.0 |
| 2025-08-24 23:00:00+02:00 | 1690.0 | 1.0 |
| 2025-08-24 23:15:00+02:00 | 1062.0 | 1.0 |
| 2025-08-24 23:30:00+02:00 | 1011.0 | 1.0 |
| 2025-08-24 23:45:00+02:00 | 1029.0 | 1.0 |
768 rows × 2 columns
In [24]:
country_code = 'DE_LU'
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption
Out[24]:
| Hydro Pumped Storage | Solar | Wind Onshore | |
|---|---|---|---|
| Actual Consumption | Actual Consumption | Actual Consumption | |
| 2025-08-17 00:00:00+02:00 | 15.0 | 0.0 | 0.0 |
| 2025-08-17 00:15:00+02:00 | 88.0 | 0.0 | 0.0 |
| 2025-08-17 00:30:00+02:00 | 21.0 | 0.0 | 0.0 |
| 2025-08-17 00:45:00+02:00 | 32.0 | 0.0 | 0.0 |
| 2025-08-17 01:00:00+02:00 | 32.0 | 0.0 | 0.0 |
| ... | ... | ... | ... |
| 2025-08-24 22:45:00+02:00 | 8.0 | 0.0 | 0.0 |
| 2025-08-24 23:00:00+02:00 | 23.0 | 0.0 | 0.0 |
| 2025-08-24 23:15:00+02:00 | 105.0 | 0.0 | 0.0 |
| 2025-08-24 23:30:00+02:00 | 105.0 | 0.0 | 0.0 |
| 2025-08-24 23:45:00+02:00 | 335.0 | 0.0 | 0.0 |
768 rows × 3 columns
In [25]:
country_code = 'FR'
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption
Out[25]:
| Energy storage | Fossil Hard coal | Fossil Oil | Hydro Pumped Storage | Wind Offshore | |
|---|---|---|---|---|---|
| Actual Consumption | Actual Consumption | Actual Consumption | Actual Consumption | Actual Consumption | |
| 2025-08-17 00:00:00+02:00 | 3.0 | 1.0 | NaN | 0.0 | NaN |
| 2025-08-17 00:15:00+02:00 | 3.0 | 1.0 | NaN | 2.0 | NaN |
| 2025-08-17 00:30:00+02:00 | 5.0 | 1.0 | NaN | 18.0 | NaN |
| 2025-08-17 00:45:00+02:00 | 3.0 | 1.0 | NaN | 26.0 | NaN |
| 2025-08-17 01:00:00+02:00 | 3.0 | 1.0 | NaN | 158.0 | NaN |
| ... | ... | ... | ... | ... | ... |
| 2025-08-24 22:45:00+02:00 | 29.0 | 1.0 | NaN | 52.0 | NaN |
| 2025-08-24 23:00:00+02:00 | 3.0 | 1.0 | NaN | 1.0 | NaN |
| 2025-08-24 23:15:00+02:00 | 5.0 | 1.0 | NaN | 1.0 | NaN |
| 2025-08-24 23:30:00+02:00 | 5.0 | 1.0 | NaN | 1.0 | NaN |
| 2025-08-24 23:45:00+02:00 | 20.0 | 1.0 | NaN | 1.0 | NaN |
768 rows × 5 columns
In [26]:
country_code = 'DE_LU'
df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False)
df_exports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= True, per_hour = False)
import_sum = df_imports.ffill().drop(columns='sum').transpose().sum()
import_sum.name='imports'
export_sum = df_exports.ffill().drop(columns='sum').transpose().sum()
export_sum.name='exports'
# df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)
dfDE = pd.concat([-1 * export_sum, -1* phs_consumption, import_sum, df.DE_LU], axis=1)
dfDE
Out[26]:
| exports | Hydro Pumped Storage | Solar | Wind Onshore | imports | biomass | brown_coal | coal_gas | demand | demand_forecast | ... | wind_offshore_forecast | wind_onshore | wind_onshore_forecast | hour | doy | doy_season | dt | wind_forecast | vre_forecast | residual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2025-08-16 22:00:00+00:00 | -3157.0 | -15.0 | -0.0 | -0.0 | 7101.0 | 3824.0 | 6345.0 | 474.0 | 39702.0 | 40436.0 | ... | 4385.0 | 11065.0 | 12005.0 | 22 | 228 | 108 | 2025-08-16 22:00:00+00:00 | 4385.0 | 4385.0 | 35317.0 |
| 2025-08-16 22:15:00+00:00 | -3295.0 | -88.0 | -0.0 | -0.0 | 7348.0 | 3813.0 | 6347.0 | 488.0 | 39010.0 | 39815.0 | ... | 4357.0 | 10948.0 | 11660.0 | 22 | 228 | 108 | 2025-08-16 22:15:00+00:00 | 4357.0 | 4357.0 | 34653.0 |
| 2025-08-16 22:30:00+00:00 | -3198.0 | -21.0 | -0.0 | -0.0 | 7648.0 | 3812.0 | 6337.0 | 481.0 | 38817.0 | 39397.0 | ... | 4318.0 | 10791.0 | 11317.0 | 22 | 228 | 108 | 2025-08-16 22:30:00+00:00 | 4318.0 | 4318.0 | 34499.0 |
| 2025-08-16 22:45:00+00:00 | -3014.0 | -32.0 | -0.0 | -0.0 | 7477.0 | 3811.0 | 6342.0 | 488.0 | 38140.0 | 38968.0 | ... | 4283.0 | 10653.0 | 11004.0 | 22 | 228 | 108 | 2025-08-16 22:45:00+00:00 | 4283.0 | 4283.0 | 33857.0 |
| 2025-08-16 23:00:00+00:00 | -2750.0 | -32.0 | -0.0 | -0.0 | 7195.0 | 3808.0 | 6344.0 | 492.0 | 37712.0 | 38496.0 | ... | 4245.0 | 10511.0 | 10678.0 | 23 | 228 | 108 | 2025-08-16 23:00:00+00:00 | 4245.0 | 4245.0 | 33467.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2025-08-24 21:00:00+00:00 | -1028.0 | -23.0 | -0.0 | -0.0 | 8869.0 | 3892.0 | 8099.0 | 427.0 | 42181.0 | 42582.0 | ... | 1700.0 | 6436.0 | 6614.0 | 21 | 236 | 116 | 2025-08-24 21:00:00+00:00 | 1700.0 | 1700.0 | 40481.0 |
| 2025-08-24 21:15:00+00:00 | -995.0 | -105.0 | -0.0 | -0.0 | 9339.0 | 3890.0 | 8046.0 | 431.0 | 41816.0 | 42077.0 | ... | 1700.0 | 6563.0 | 6597.0 | 21 | 236 | 116 | 2025-08-24 21:15:00+00:00 | 1700.0 | 1700.0 | 40116.0 |
| 2025-08-24 21:30:00+00:00 | -934.0 | -105.0 | -0.0 | -0.0 | 9450.0 | 3891.0 | 8029.0 | 448.0 | 41434.0 | 41484.0 | ... | 1696.0 | 6549.0 | 6575.0 | 21 | 236 | 116 | 2025-08-24 21:30:00+00:00 | 1696.0 | 1696.0 | 39738.0 |
| 2025-08-24 21:45:00+00:00 | -826.0 | -335.0 | -0.0 | -0.0 | 9843.0 | 3883.0 | 7914.0 | 459.0 | 41358.0 | 40993.0 | ... | 1693.0 | 6657.0 | 6555.0 | 21 | 236 | 116 | 2025-08-24 21:45:00+00:00 | 1693.0 | 1693.0 | 39665.0 |
| 2025-08-24 22:00:00+00:00 | NaN | NaN | NaN | NaN | NaN | 3883.0 | 7914.0 | 459.0 | 41358.0 | 40993.0 | ... | 1693.0 | 6657.0 | 6555.0 | 22 | 236 | 116 | 2025-08-24 22:00:00+00:00 | 1693.0 | 1693.0 | 39665.0 |
769 rows × 39 columns
In [27]:
# fig = df_exports.ffill().drop(columns='sum').transpose().sum().plot()
# data = df_exports.drop(columns='sum').transpose().sum().plot().data[0]
# data.line.color = 'red'
# fig.add_trace(data)
In [28]:
fig = dfDE.drop(columns=non_generation).plot(kind='area')
fig.add_trace(go.Scatter(y=df.DE_LU.demand, x=df.DE_LU.dt, mode='lines',line=dict(color='black')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
fig.add_trace(go.Scatter(y=df.DE_LU.demand_forecast, x=df.DE_LU.dt, mode='lines',line=dict(color='gray')))
In [29]:
country_code = 'FR'
df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False)
df_exports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= True, per_hour = False)
import_sum = df_imports.ffill().drop(columns='sum').transpose().sum()
import_sum.name='imports'
export_sum = df_exports.ffill().drop(columns='sum').transpose().sum()
export_sum.name='exports'
# df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)
df_plot = pd.concat([-1 * export_sum, -1* phs_consumption, import_sum, df[country_code]], axis=1)
df_plot
Out[29]:
| exports | Energy storage | Fossil Hard coal | Fossil Oil | Hydro Pumped Storage | Wind Offshore | imports | biomass | brown_coal | coal_gas | ... | wind_offshore_forecast | wind_onshore | wind_onshore_forecast | hour | doy | doy_season | dt | wind_forecast | vre_forecast | residual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2025-08-16 22:00:00+00:00 | -15078.0 | -3.0 | -1.0 | NaN | -0.0 | NaN | 0.0 | 228.0 | 0.0 | 0.0 | ... | 1214.0 | 5612.0 | 6918.0 | 22 | 228 | 108 | 2025-08-16 22:00:00+00:00 | 1214.0 | 1214.0 | 39230.0 |
| 2025-08-16 22:15:00+00:00 | -14432.0 | -3.0 | -1.0 | NaN | -2.0 | NaN | 0.0 | 234.0 | 0.0 | 0.0 | ... | 1214.0 | 5485.0 | 6918.0 | 22 | 228 | 108 | 2025-08-16 22:15:00+00:00 | 1214.0 | 1214.0 | 38061.0 |
| 2025-08-16 22:30:00+00:00 | -14563.0 | -5.0 | -1.0 | NaN | -18.0 | NaN | 0.0 | 236.0 | 0.0 | 0.0 | ... | 1214.0 | 5476.0 | 6918.0 | 22 | 228 | 108 | 2025-08-16 22:30:00+00:00 | 1214.0 | 1214.0 | 36803.0 |
| 2025-08-16 22:45:00+00:00 | -14567.0 | -3.0 | -1.0 | NaN | -26.0 | NaN | 0.0 | 237.0 | 0.0 | 0.0 | ... | 1214.0 | 5436.0 | 6918.0 | 22 | 228 | 108 | 2025-08-16 22:45:00+00:00 | 1214.0 | 1214.0 | 35846.0 |
| 2025-08-16 23:00:00+00:00 | -14158.0 | -3.0 | -1.0 | NaN | -158.0 | NaN | 0.0 | 239.0 | 0.0 | 0.0 | ... | 1230.0 | 5420.0 | 6424.0 | 23 | 228 | 108 | 2025-08-16 23:00:00+00:00 | 1230.0 | 1230.0 | 36012.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2025-08-24 21:00:00+00:00 | -14530.0 | -3.0 | -1.0 | NaN | -1.0 | NaN | 0.0 | 227.0 | 0.0 | 0.0 | ... | 700.0 | 3453.0 | 3320.0 | 21 | 236 | 116 | 2025-08-24 21:00:00+00:00 | 700.0 | 700.0 | 39587.0 |
| 2025-08-24 21:15:00+00:00 | -14486.0 | -5.0 | -1.0 | NaN | -1.0 | NaN | 0.0 | 227.0 | 0.0 | 0.0 | ... | 700.0 | 3550.0 | 3320.0 | 21 | 236 | 116 | 2025-08-24 21:15:00+00:00 | 700.0 | 700.0 | 38771.0 |
| 2025-08-24 21:30:00+00:00 | -14306.0 | -5.0 | -1.0 | NaN | -1.0 | NaN | 0.0 | 227.0 | 0.0 | 0.0 | ... | 700.0 | 3627.0 | 3320.0 | 21 | 236 | 116 | 2025-08-24 21:30:00+00:00 | 700.0 | 700.0 | 38917.0 |
| 2025-08-24 21:45:00+00:00 | -14338.0 | -20.0 | -1.0 | NaN | -1.0 | NaN | 0.0 | 226.0 | 0.0 | 0.0 | ... | 700.0 | 3709.0 | 3320.0 | 21 | 236 | 116 | 2025-08-24 21:45:00+00:00 | 700.0 | 700.0 | 38091.0 |
| 2025-08-24 22:00:00+00:00 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 226.0 | 0.0 | 0.0 | ... | 700.0 | 3709.0 | 3320.0 | 22 | 236 | 116 | 2025-08-24 22:00:00+00:00 | 700.0 | 700.0 | 38091.0 |
769 rows × 41 columns
In [30]:
fig = df_plot.drop(columns=non_generation).plot(kind='area')
fig.add_trace(go.Scatter(y=df[country_code].demand, x=df[country_code].dt, mode='lines',line=dict(color='black')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
fig.add_trace(go.Scatter(y=df[country_code].demand_forecast, x=df[country_code].dt, mode='lines',line=dict(color='gray')))
In [31]:
start = pd.Timestamp('20240630', tz='Europe/Brussels')
end = pd.Timestamp('20250630', tz='Europe/Brussels')
period = slice(start,end)
# df_FR = df.FR.loc[period]
df_FR = df.FR
data = []
data.append(
go.Scatter(
x=df_FR.index,
y=df_FR.demand,
line=dict(
width=0.5,
color=light_blue_gray,
),
showlegend=False,
)
)
data.append(
go.Scatter(
x=df_FR.index,
y=df_FR.demand.rolling(window=24 * 7*4, center=True, min_periods=24*7).mean(),
line=dict(
color=fca_blue,
),
name="1 month moving average",
)
)
In [32]:
layout = go.Layout(
template=fca_template,
title_text="Electricity consumption France (MW)",
yaxis_range=[0,89e3]
)
fig = go.Figure(data=data, layout=layout)
fig.show()
In [33]:
layout = go.Layout(
template=fca_template,
title_text="Electricity consumption over one week, France (MW)",
yaxis_range=[0,89e3],
xaxis_range=[
pd.Timestamp('2024-08-18 22:00:00'),
pd.Timestamp('2024-08-26 4:00:00')
]
)
fig = go.Figure(data=data, layout=layout)
fig.show()
In [34]:
px.density_heatmap(
df_FR,
x="hour",
y="demand",
# y="weighted_market_val",
nbinsy=60,
nbinsx=24,
color_continuous_scale=cm_data_plotly,
).update_layout(template=fca_template, title_text='Frequency of demand by hour, France (green-yellow = most frequent values)')
In [ ]:
In [35]:
start = pd.Timestamp('20240101', tz='Europe/Brussels')
end = pd.Timestamp('20250101', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
df_DE.price.mean()
Out[35]:
nan
Levelized cost of Heat/Hydrogen¶
Problems with this:
- You can't know in advance what the percentile of the current price hour will be
- negative price hours might vanish in the future
- the period must be multiples of one year, as otherwise you oversample summmers and undersample winters
- this uses NREL-style LCOE, with no discounting for OPEX
- There are no grid fees & other levies/taxes
- there are no transport & storage costs
In [36]:
start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
# 0.109 is the capital recovery factor
avgs = []
for q in np.linspace(0,1,500):
quant = df_DE.price.quantile(q)
mean = df_DE.query('price <= @quant').price.mean()
# avgs.append(mean if mean >= 0 else 0)
avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs / 1.0, # 100% efficiency, roughly cancels out with gas price being GCV ~50/55?
name="average Electricity cost 1MWh_heat",
line_color="black",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="1000 EUR/kW",
line_color=blue_gray,
line_dash="dot",
)
)
# fig.add_trace(go.Scatter(x=np.linspace(1/500,1,100), y=500e3*0.109/(365*24*np.linspace(1/500,1,100)), name='500 EUR/kW' ))
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="500 EUR/kW",
line_color=dark_gray,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="250 EUR/kW",
line_color=fca_blue,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=100e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="100 EUR/kW",
line_color=highlight_blue,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs
+ 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
showlegend=False,
line_color=blue_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs
+ 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
showlegend=False,
line_color=dark_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs
+ 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="LCoHeat for respective capital cost",
line_color=fca_blue,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs
+ 100e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
showlegend=False,
line_color=highlight_blue,
)
)
# fig.add_trace(go.Scatter(x=np.linspace(1/500,1,500), y=df_DE.price.quantile(np.linspace(1/500,1,500)) + 500e3*0.109/(365*24*np.linspace(1/500,1,500)),name='LCoH'))
fig.update_layout(
dict(
width=800,
title="LCOHeat for Germany, 01/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
xaxis_title="price hour percentile",
yaxis_title="eur/MWh",
)
)
fig.update_layout(template=fca_template, yaxis_range=[-25,110], xaxis_range=[0,1.19], legend=dict(x=1, y=0.6))
fig.show()
In [37]:
start = pd.Timestamp('20240101', tz='Europe/Brussels')
end = pd.Timestamp('20250101', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
df_DE.price.mean()
Out[37]:
nan
Hydrogen levelized cost¶
In [38]:
start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
# 0.109 is the capital recovery factor
avgs = []
for q in np.linspace(0,1,500):
quant = df_DE.price.quantile(q)
mean = df_DE.query('price <= @quant').price.mean()
# avgs.append(mean if mean >= 0 else 0)
avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()
# variable cost
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs / 0.73, # 73% efficiency lhv
name="average Electricity cost 1MWh_H2_lhv 72% eff.",
line_color="black",
)
)
# indicate 2 eur/kg price level
fig.add_trace(
go.Scatter(
x=np.linspace(0,1,100),
y=[60.024,60.024]*50, # 2 eur/kg /(33.32kWh/kg) in eur/MWh = 60.024 EUR / MWh
name="2 EUR/kg_H2",
line_color="black",
mode='lines',
line_dash='dot',
)
)
# fixed cost from capital (+O&M, implicitly)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="2500 EUR/kW",
line_color=highlight_blue,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="1000 EUR/kW",
line_color=blue_gray,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="500 EUR/kW",
line_color=dark_gray,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="250 EUR/kW",
line_color=fca_blue,
line_dash="dot",
)
)
# variable cost added to fixed
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
showlegend=False,
name="2500 EUR/kW",
line_color=highlight_blue,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="1000 EUR/kW",
showlegend=False,
line_color=blue_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="500 EUR/kW",
showlegend=False,
line_color=dark_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="LCoH for capital cost (four values)",
line_color=fca_blue,
)
)
fig.update_layout(
dict(
width=800,
title="LCOHydrogen for Germany, 08/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
xaxis_title="price hour percentile",
yaxis_title="eur/MWh",
)
)
fig.update_layout(template=fca_template, yaxis_range=[-25,150], xaxis_range=[0,1.24], legend=dict(x=1.8, y=0.6))
fig.show()
In [39]:
# trying with clamped prices
In [40]:
start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
# 0.109 is the capital recovery factor
avgs = []
for q in np.linspace(0,1,500):
quant = df_DE.price.quantile(q)
mean = df_DE.query('price <= @quant').price.mean()
avgs.append(mean if mean >= 0 else 0)
#avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()
# variable cost
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs / 0.73, # 73% efficiency lhv
name="average Electricity cost 1MWh_H2_lhv 72% eff.",
line_color="black",
)
)
# indicate 2 eur/kg price level
fig.add_trace(
go.Scatter(
x=np.linspace(0,1,100),
y=[60.024,60.024]*50, # 2 eur/kg /(33.32kWh/kg) in eur/MWh = 60.024 EUR / MWh
name="2 EUR/kg_H2",
line_color="black",
mode='lines',
line_dash='dot',
)
)
# fixed cost from capital (+O&M, implicitly)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="2500 EUR/kW",
line_color=highlight_blue,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="1000 EUR/kW",
line_color=blue_gray,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="500 EUR/kW",
line_color=dark_gray,
line_dash="dot",
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="250 EUR/kW",
line_color=fca_blue,
line_dash="dot",
)
)
# variable cost added to fixed
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
showlegend=False,
name="2500 EUR/kW",
line_color=highlight_blue,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="1000 EUR/kW",
showlegend=False,
line_color=blue_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="500 EUR/kW",
showlegend=False,
line_color=dark_gray,
)
)
fig.add_trace(
go.Scatter(
x=np.linspace(1/500, 1, 500),
y=avgs /0.73
+ 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
name="LCoH for capital cost (four values)",
line_color=fca_blue,
)
)
fig.update_layout(
dict(
width=800,
title="LCOHydrogen for Germany, 08/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
xaxis_title="price hour percentile",
yaxis_title="eur/MWh",
)
)
fig.update_layout(template=fca_template, yaxis_range=[-25,150], xaxis_range=[0,1.24], legend=dict(x=1.8, y=0.6))
fig.show()
fix me¶
In [41]:
df_EU = df
# # only 202X
y0 = 2022
y1 = 2023
# period = slice(f'{y0}-01-01 00:00:00',f'{y1}-01-01 00:00:00')
df_DE_2 = df_EU.DE_LU.copy()
y0, y1 = 2023, 2024
df23 = (df_DE_2
.copy()
.loc[slice(f'{y0}-01-01 00:00:00', f'{y1}-01-01 00:00:00')]
.assign(vre = lambda df: df.vre_forecast)
.assign(wind = lambda df: df.wind_forecast)
.assign(solar = lambda df: df.solar_forecast)
.assign(market_val = lambda df: df.price * df.vre / df.demand)
.assign(vre_quant = lambda df: df.vre/df.vre.max())
)
vre_mean = df23.vre.mean()
vre_quant = df23.vre.quantile(0.75)
bin_width = 1000
df_DE_2 = df23.copy()
df_DE_2_vre = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').price.describe()
df_DE_2_means = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').mean()
df_DE_2_median = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').median()
# df_DE_2_wind = df_DE_2.assign(wind_bin=lambda df: np.floor((df.wind / bin_width))*bin_width).groupby('wind_bin').mean()
df_DE_2_wind = df_DE_2.assign(wind_bin=lambda df: np.floor((df.wind / bin_width))*bin_width).groupby('wind_bin').price.describe()
# df_DE_2_wind['mean']
df_DE_2_wind.head()
# df_DE_2_solar = df_DE_2.assign(solar_bin=lambda df: np.floor((df.solar / bin_width))*bin_width).groupby('solar_bin').mean()
df_DE_2_solar = df_DE_2.assign(solar_bin=lambda df: np.floor((df.solar / bin_width))*bin_width).groupby('solar_bin').price.describe()
# df_DE_2_solar['mean']
df_DE_2_solar['25%']
fig = go.Figure()
scatter = go.Scatter(
x=df_DE_2.vre,
y=df_DE_2.market_val,
mode='markers',
marker=dict(opacity=0.1, size=3, color=gray),
name='Hourly market value (VRE production × day ahead price)',
# y="weighted_market_val",
# nbinsy=3000,
# nbinsx=1000,
# color_continuous_scale="magma",
)
fig.update_layout(
template=fca_template,
yaxis_range=[-0.34e6, 4.2e6],
xaxis_range=[-1000, 62e3],
yaxis_title_text='Market value (€)',
xaxis_title_text='VRE production (MW)',
title=f"Market value of VREs declining with rising share of generation in Germany from {y0} to {y1}",
legend=dict(
x=1.1,
y=1,
xanchor='left',
yanchor='top',
),
height=540*1.4,
width=960*1.4,
)
# fig.add_trace(scatter)
fig.add_trace(
go.Scatter(
x=[0, df_DE_2.vre.max()],
y=[0, df_DE_2.price.mean() * df_DE_2.vre.max()],
mode="lines",
line_color=very_dark_gray,
line_width=4,
name=f'VRE production × Mean price {y0}-{y1}'
)
)
fig.add_vline(x=df_DE_2.demand.mean(), annotation=dict(text='mean demand'))
green = 'rgba(145, 192, 150, 1)'
fig.add_trace(
go.Scatter(
x=df_DE_2_means.index,
y=df_DE_2_means.price*df_DE_2_means.index,
mode="lines",
line_color=green,
line_width=4,
name='Mean VRE market value'
)
)
green = 'rgba(145, 192, 150, 0.2)'
fig.add_traces(
go.Scatter(
x=df_DE_2_vre["25%"].index,
y=df_DE_2_vre["25%"] * df_DE_2_vre["25%"].index,
line=dict(color=green),
name='IQR',
)
)
fig.add_traces(
go.Scatter(
x=df_DE_2_vre["75%"].index,
y=df_DE_2_vre["75%"] * df_DE_2_vre["75%"].index,
fill="tonexty",
fillcolor=green,
line=dict(color=green),
showlegend=False,
)
)
sand_yellow = 'rgba(226, 182, 129, 1)'
fig.add_trace(
go.Scatter(
x=df_DE_2_solar.index,
y=df_DE_2_solar['mean']*df_DE_2_solar.index,
mode="lines",
line_color=sand_yellow,
line_width=4,
name='Mean solar market value'
)
)
sand_yellow = 'rgba(226, 182, 129, 0.2)'
fig.add_traces(
go.Scatter(
x=df_DE_2_solar["25%"].index,
y=df_DE_2_solar["25%"] * df_DE_2_solar["25%"].index,
line=dict(color=sand_yellow),
name='IQR',
)
)
fig.add_traces(
go.Scatter(
x=df_DE_2_solar["75%"].index,
y=df_DE_2_solar["75%"] * df_DE_2_solar["75%"].index,
fill="tonexty",
fillcolor=sand_yellow,
line=dict(color=sand_yellow),
showlegend=False,
)
)
highlight_blue = 'rgba(2, 147, 210, 1)'
fig.add_trace(
go.Scatter(
x=df_DE_2_wind.index,
y=df_DE_2_wind['mean']*df_DE_2_wind.index,
mode="lines",
line_color=highlight_blue,
line_width=4,
name='Mean wind market value'
)
)
highlight_blue = 'rgba(2, 147, 210, 0.2)'
fig.add_traces(
go.Scatter(
x=df_DE_2_wind["25%"].index,
y=df_DE_2_wind["25%"] * df_DE_2_wind["25%"].index,
line=dict(color=highlight_blue),
name='IQR',
)
)
fig.add_traces(
go.Scatter(
x=df_DE_2_wind["75%"].index,
y=df_DE_2_wind["75%"] * df_DE_2_wind["75%"].index,
fill="tonexty",
fillcolor=highlight_blue,
opacity=0.1,
line=dict(color="rgba(0,0,0,0)"),
showlegend=False,
)
)
fig.show()
In [ ]: